package com.uchedao.seller.ui.carlist.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.uchedao.seller.R;
import com.uchedao.seller.ui.carlist.entity.BrandEntity;
import com.uchedao.seller.ui.carlist.entity.CityEntity;
import com.uchedao.seller.ui.carlist.entity.ModelEntity;
import com.uchedao.seller.ui.carlist.entity.ProvinceEntity;
import com.uchedao.seller.ui.carlist.entity.SeriesEntity;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * Created by ZhangZhanghua on 2015/8/24 0024.
 */
public class CarDbHelper extends SQLiteOpenHelper {
    public static final String KEY_ID = "id";
    public static final String KEY_FIRST = "char";
    public static final String KEY_NAME = "name";
    public static final String KEY_FULL_SPELL = "full_spell";
    public static final String KEY_ICON_PATH= "icon_path";
    //    id	int	ID
    //    char	string	首字母 别名initial
    //    name	string	名称 别名text
    //    full_spell	string	全拼
    //    icon_path	string	icon url
    //    path	string
    public static final String KEY_BRAND_ID = "brand_id";
    public static final String KEY_TYPE_ID = "type_id";
    public static final String KEY_IMPORT_ID = "import_id";

//    id	int	ID
//    brand_id	int	品牌ID
//    series_id	int	厂商ID
//    full_spell	string	拼音
//    type_id	int	车类型
//    import_id	int	1进口 2合资 3国产
//    char	string	首字母 别名initial
//    full_name	string	全拼
//    name	string	名称 别名text
//    path	string	网站路径
    public static final String KEY_SERIES_ID = "series_id";
    public static final String KEY_MODEL_YEAR = "model_year";
//    id	int	ID
//    name	int	名称 别名text
//    model_year	int	年款
//    series_id  int 车系ID

    private static final String DB_NAME = "uchedao";

    public static final String TABLE_NAME_BRAND = "car_brand";

    public static final String TABLE_NAME_SERIES = "car_series";

    public static final String TABLE_NAME_MODEL = "car_model";

    public static final String TABLE_NAME_PROVINCE = "province";

    public static final String TABLE_NAME_CITY = "city";
    //    id	int	ID
//    first_char	string	首字母
//    name	string	名称
//    full_spell	string	全拼
    private static final String KEY_FIRST_CHAR = "frist_char";

    private static final String  KEY_PROVINCE_ID = "province_id";

    private static final int VER_INT = 2;

//    "id":128,
// "char":"A",
// "name":"\u963f\u5c14\u6cd5-\u7f57\u5bc6\u6b27",
// "full_spell":"aerfazzzluomiou",
// "icon_path ":"http:\/\/att.273.com.cn\/brand_icon\/128.png","
    // 品牌
    private static final String BRAND_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" + TABLE_NAME_BRAND + "] ( ["
            + KEY_ID + "] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_NAME + "] VARCHAR(32), ["
            + KEY_FULL_SPELL + "] VARCHAR(32),[" + KEY_FIRST + "] VARCHAR(32),[" + KEY_ICON_PATH + "] VARCHAR(32)); ";

//    "id":22
// "brand_id":5
// "maker_id":5
// "name":"SPRINGO"
// "full_spell":"springo"
// "type_id":1
// "import_id":3
// "char":"S"
    // 系列
    private static final String SERIES_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" + TABLE_NAME_SERIES + "] ( ["
            + KEY_ID + "] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_BRAND_ID + "] INTEGER, ["
            + KEY_FULL_SPELL + "] VARCHAR(32), [" + KEY_NAME + "] VARCHAR(32),[" + KEY_TYPE_ID + "] INTEGER,[" + KEY_IMPORT_ID + "] INTEGER); ";

//"id":285903
// "series_id":2145
// "name":"\u6b3e 2.8 \u624b\u52a8 Z7 "
// "model_year":""
    // 型号
    private static final String MODEL_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" + TABLE_NAME_MODEL + "] ( ["
            + KEY_ID + "] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_SERIES_ID + "] INTEGER,["
            + KEY_MODEL_YEAR + "] VARCHAR(32), [" + KEY_NAME + "] VARCHAR(32)); ";

//    "id":47,
// "name":"安徽"
// "first_char":"a"
// "full_spell":"anhui"
    private static final String PROVINCE_TABLE_SQL_CREATOR =  "CREATE TABLE IF NOT EXISTS [" +TABLE_NAME_PROVINCE +"] ( ["
        + KEY_ID +"] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" +KEY_NAME +"] VARCHAR(32), ["
        +KEY_FIRST_CHAR +"] VARCHAR(32), ["+ KEY_FULL_SPELL +"] VARCHAR(32)); ";

    private static final String CITY_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" +TABLE_NAME_CITY +"]" +
            "( ["+ KEY_ID +"] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" +KEY_PROVINCE_ID +"] INTEGER, [" +KEY_NAME +"] VARCHAR(32), ["+KEY_FIRST_CHAR +"] VARCHAR(32), " +
            "["+ KEY_FULL_SPELL +"] VARCHAR(32));";

    private SQLiteDatabase db;

    private static CarDbHelper carDbHelper;

    public static CarDbHelper getInstance(Context context) {
        if (carDbHelper == null)
            carDbHelper = new CarDbHelper(context);
        return carDbHelper;
    }

    public CarDbHelper(Context context) {
        super(context, DB_NAME, null, VER_INT);
        db = getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;
        db.execSQL(BRAND_TABLE_SQL_CREATOR);
        db.execSQL(SERIES_TABLE_SQL_CREATOR);
        db.execSQL(MODEL_TABLE_SQL_CREATOR);
        db.execSQL(PROVINCE_TABLE_SQL_CREATOR);
        db.execSQL(CITY_TABLE_SQL_CREATOR);
    }

    public SQLiteDatabase getDb() {
        return db;
    }

    /**
     * 插入品牌记录
     */
    public void insertBrand(int id, String name, String spell, String first, String iconPath, int isHot) {
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_NAME, name);
            values.put(KEY_FULL_SPELL, spell);
            values.put(KEY_FIRST,first);
            values.put(KEY_ICON_PATH,iconPath);
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_BRAND, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 插入系列记录
     */
    public void insertSeries(int id, int brandId, String name, String spell, int typeId, int importId) {
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_BRAND_ID, brandId);
            values.put(KEY_NAME, name);
            values.put(KEY_FULL_SPELL, spell);
            values.put(KEY_TYPE_ID, typeId);
            values.put(KEY_IMPORT_ID, importId);

            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_SERIES, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 插入车型记录
     */
    public void insertModel(int id, int seriesId, String name, String modelYear) {
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_SERIES_ID, seriesId);
            values.put(KEY_NAME, name);
            values.put(KEY_MODEL_YEAR, modelYear);
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_MODEL, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 插入省份
     */
    public void insertProvince(int id, String name, String firstChar, String spell){
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_NAME, name);
            values.put(KEY_FIRST_CHAR, firstChar);
            values.put(KEY_FULL_SPELL, spell);
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_PROVINCE, null, values);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 插入城市
     */
    public void insertCity(int id, int provinceId, String name, String firstChar, String spell){
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_PROVINCE_ID, provinceId);
            values.put(KEY_NAME, name);
            values.put(KEY_FIRST_CHAR, firstChar);
            values.put(KEY_FULL_SPELL, spell);
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_CITY, null, values);
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    /**
     * 记录是否存在
     *
     * @param tableName 表名
     * @param id        id
     * @return
     */
    public boolean hasRecord(String tableName, int id) {
        Cursor c = queryId(tableName, id);
        if (c == null)
            return false;
        boolean flag = c.getCount() > 0;
        c.close();
        c = null;
        return flag;
    }

    /**
     * 查询id记录是否存在
     *
     * @param tableName 表名
     * @param id        id
     * @return
     */
    public Cursor queryId(String tableName, int id) {
        if (db == null)
            db = getWritableDatabase();
        if (!db.isOpen())
            return null;
        return db.rawQuery("select * from " + tableName + " where " + KEY_ID + "=?",
                new String[]{String.valueOf(id)});
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void rawQuery(String sql, String[] selectionArgs) {
        db.rawQuery(sql, selectionArgs);
    }

    public void beginTransaction() {
        db.beginTransaction();
    }

    public void setTransactionSuccessful() {
        db.setTransactionSuccessful();
    }

    public void endTransaction() {
        db.endTransaction();
    }

    /**
     * 删除所有表
     */
    public void deleteAllTable() {
        db.delete(TABLE_NAME_BRAND, null, null);
        db.delete(TABLE_NAME_SERIES, null, null);
        db.delete(TABLE_NAME_MODEL, null, null);
    }


    /**
     * 得到汽车品牌列表
     *
     * @param cxt
     * @return
     */
    public static ArrayList<BrandEntity> getCarBrand(Context cxt) {
        ArrayList<BrandEntity> array = new ArrayList<BrandEntity>();
        Cursor c = getInstance(cxt).getWritableDatabase().rawQuery(BrandEntity.SEARCH_ALL_BRIND, null);
        BrandEntity brandEntity = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                brandEntity = new BrandEntity();
                brandEntity.setId(c.getInt(0));
                brandEntity.setName(c.getString(1));
                brandEntity.setFull_spell(c.getString(2));
                brandEntity.setInitial(c.getString(3));
                array.add(brandEntity);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return array;
    }

    /**
     * 得到车系列表
     *
     * @param cxt
     * @return
     */
    public static ArrayList<SeriesEntity> getCarSeries(Context cxt, int brandId) {
        ArrayList<SeriesEntity> array = new ArrayList<SeriesEntity>();
        Cursor c = getInstance(cxt).getWritableDatabase().rawQuery(SeriesEntity.SEARCH_SERIES_BY_BRANDE_ID,
                new String[]{String.valueOf(brandId)});
        SeriesEntity seriesEntity = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                seriesEntity = new SeriesEntity();
                seriesEntity.setId(c.getInt(0));
                seriesEntity.setBrand_id(c.getInt(1));
                seriesEntity.setName(c.getString(3));
                seriesEntity.setFull_spell(c.getString(2));
                seriesEntity.setType_id(c.getInt(4));
                seriesEntity.setImport_id(c.getInt(5));
                array.add(seriesEntity);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return array;
    }

    /**
     * 得到车型列表
     *
     * @param cxt
     * @return
     */
    public static ArrayList<ModelEntity> getCarModel(Context cxt, int seriesId) {
        ArrayList<ModelEntity> array = new ArrayList<ModelEntity>();
        Cursor c = getInstance(cxt).getWritableDatabase().rawQuery(ModelEntity.SELECT_MODEL,
                new String[]{String.valueOf(seriesId)});
        ModelEntity modelEntity = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                modelEntity = new ModelEntity();
                modelEntity.setId(c.getInt(0));
                modelEntity.setSeries_id(c.getInt(1));
                modelEntity.setModel_year(c.getString(2));
                modelEntity.setName(c.getString(3));
                array.add(modelEntity);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return array;
    }

    /**
     * 获取省份列表
     * @param context
     * @return
     */
    public static ArrayList<ProvinceEntity> getProvince(Context context){
        ArrayList<ProvinceEntity> arrayList = new ArrayList<ProvinceEntity>();
        Cursor c = getInstance(context).getWritableDatabase().rawQuery(ProvinceEntity.SEARCH_PROVINCE,null);
        ProvinceEntity entity = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                entity = new ProvinceEntity();
                entity.setId(c.getInt(0));
                entity.setName(c.getString(1));
                entity.setFirst_char(c.getString(2));
                entity.setFull_spell(c.getString(3));
                arrayList.add(entity);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return arrayList;
    }

    /**
     * 获取城市列表
     * @param context
     * @param provinceId
     * @return
     */
    public static ArrayList<CityEntity> getCity(Context context,int provinceId){
        ArrayList<CityEntity> arrayList = new ArrayList<CityEntity>();
        Cursor c = getInstance(context).getWritableDatabase().rawQuery(CityEntity.SEARCH_CITY_BY_PROVINCE_ID,new String[]{String.valueOf(provinceId)});
        CityEntity entity = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                entity = new CityEntity();
                entity.setId(c.getInt(0));
                entity.setProvince_id(c.getInt(1));
                entity.setName(c.getString(2));
                entity.setFirst_char(c.getString(3));
                entity.setFull_spell(c.getString(4));
                arrayList.add(entity);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return arrayList;
    }
    /**
     * 导入数据库文件
     *
     * @param context
     * @return
     */
    public static boolean importDatabase(Context context) {

        boolean flag = true;
        // 存放数据库的目录
        String dirPath = "/data/data/" + context.getPackageName() + "/databases";
        File dir = new File(dirPath);
        if (!dir.exists()) {
            if (!dir.mkdir())
                return false;
        }
        if (!flag)
            return false;
        // 数据库文件
        File file = new File(dir, DB_NAME);

        if (file.exists()) {
//            if (!AppInfoManager.verUpdate) // 若版本没更新
//                return true;

            if (!file.delete()) {
                return false;
            }
        }
        try {
            if (!file.createNewFile()) {
                return false;
            }
            // 加载需要导入的数据库
            InputStream is = context.getResources().openRawResource(R.raw.uchedao);
            FileOutputStream fos = new FileOutputStream(file);
            byte[] buffere = new byte[is.available()];
            is.read(buffere);
            fos.write(buffere);
            is.close();
            fos.close();
            return true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }
}
